﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Data;
using System.Data.SqlClient;

namespace ImportDatabase
{
    class Importer
    {
        private FileInfo categorieFile;
        public FileInfo CategorieFile
        {
            private set
            {
                if (value == null)
                {
                    throw new ArgumentException();
                }
                if (!value.Exists)
                {
                    throw new ImportException("Bestand: " + value.FullName + " bestaat niet.");
                }
                categorieFile = value;
            }
            get { return categorieFile; }
        }
        private FileInfo uitgeverFile;
        public FileInfo UitgeverFile
        {
            private set
            {
                if (value == null)
                {
                    throw new ArgumentException();
                }
                if (!value.Exists)
                {
                    throw new ImportException("Bestand: " + value.FullName + " bestaat niet.");
                }
                this.uitgeverFile = value;
            }
            get { return uitgeverFile; }
        }
        private FileInfo boekenFile;
        public FileInfo BoekenFile
        {
            private set
            {
                if (value == null)
                {
                    throw new ArgumentException();
                }
                if (!value.Exists)
                {
                    throw new ImportException("Bestand: " + value.FullName + " bestaat niet.");
                }
                boekenFile = value;
            }
            get { return boekenFile; }
        }
        private SqlConnection connection;
        public SqlConnection Connection
        {
            get
            {
                return connection;
            }
            set
            {
                if (value == null)
                {
                    throw new ArgumentException();
                }
                try
                {
                    value.Open();
                }
                catch (Exception ex)
                {
                    throw new ImportException(ex);
                }
                connection = value;
            }
        }

        public Importer(FileInfo categorieFile, FileInfo uitgeverFile, FileInfo boekenFile, SqlConnection connection)
        {
            CategorieFile = categorieFile;
            UitgeverFile = uitgeverFile;
            BoekenFile = boekenFile;
            Connection = connection;
        }

        public void Close()
        {
            if (connection.State != ConnectionState.Open)
            {
                throw new ImportException();
            }
            connection.Close();
        }

        public void readAll()
        {
            if (connection.State != ConnectionState.Open)
            {
                throw new ImportException();
            }
            readCategorie();
            readUitgever();
            readBoeken();
        }

        private void readCategorie()
        {
            char[] separator = { '\t' };
            StreamReader reader = File.OpenText(CategorieFile.FullName);
            String currentLine;
            while ((currentLine = reader.ReadLine()) != null)
            {
                try{
                String[] split = currentLine.Split(separator);
                if (split.Length != 2)
                {
                    throw new ImportException("ongeldige categorie: " + currentLine);
                }
                String code = split[0].Trim();
                String naam = split[1].Trim();
                writeCategorie(code, naam);
                }
                catch (Exception ex)
                {
                }
            }
        }

        private void writeCategorie(String code, String naam)
        {
            //Console.WriteLine("adding categorie: " + code + ", " + naam);
            using (SqlCommand sqlCmd = new SqlCommand("INSERT INTO Categorie (code, naam) VALUES (@code, @naam)", Connection))
            {
                sqlCmd.Parameters.AddWithValue("@code", code);
                sqlCmd.Parameters.AddWithValue("@naam", naam);
                sqlCmd.ExecuteNonQuery();
            }
        }

        private void readUitgever()
        {
            char[] separator = { '\t' };
            StreamReader reader = File.OpenText(UitgeverFile.FullName);
            String currentLine;
            while ((currentLine = reader.ReadLine()) != null)
            {
                try
                {
                    String[] split = currentLine.Split(separator);
                    if (split.Length != 2)
                    {
                        throw new ImportException("ongeldige uitgever: " + currentLine);
                    }
                    String code = split[0].Trim();
                    String naam = split[1].Trim();
                    writeUitgever(code, naam);
                }
                catch (Exception ex)
                {
                }
            }
        }

        private void writeUitgever(String code, String naam)
        {
            //Console.WriteLine("adding uitgever: " + code + ", " + naam);
            using (SqlCommand sqlCmd = new SqlCommand("INSERT INTO Uitgever (code, naam) VALUES (@code, @naam)", Connection))
            {
                sqlCmd.Parameters.AddWithValue("@code", code);
                sqlCmd.Parameters.AddWithValue("@naam", naam);
                sqlCmd.ExecuteNonQuery();
            }
        }

        private void readBoeken()
        {
            char[] separator = { '\t' };
            StreamReader reader = File.OpenText(BoekenFile.FullName);
            String currentLine;
            while ((currentLine = reader.ReadLine()) != null)
            {
                try
                {
                    String[] split = currentLine.Split(separator);
                    if (split.Length != 6)
                    {
                        throw new ImportException("ongeldig boek: " + currentLine);
                    }
                    String titel = split[0].Trim();
                    long isbn = long.Parse(split[1].Trim());
                    String categorie = split[2].Trim();
                    int catalogus = (int)(float.Parse(split[3].Trim()) * 1000);
                    int status = int.Parse(split[4].Trim());
                    String uitgever = split[5].Trim();
                    writeBoek(titel, isbn, categorie, catalogus, status, uitgever);
                }
                catch (Exception ex)
                {
                }
            }
        }

        private void writeBoek(String titel, long isbn, String categorie, int catalogus, int status, String uitgever)
        {
            //Console.WriteLine("adding boek: " + titel + ", " + isbn + ", " + categorie + ", " + catalogus + ", " + status + ", " + uitgever);
            using (SqlCommand sqlCmd = new SqlCommand("INSERT INTO Boeken (titel, isbnnr, categoriecode, catalogusprijs, status, uitgevercode) VALUES (@titel, @isbnnr, @categoriecode, @catalogusprijs, @status, @uitgevercode)", Connection))
            {
                sqlCmd.Parameters.AddWithValue("@titel", titel);
                sqlCmd.Parameters.AddWithValue("@isbnnr", isbn);
                sqlCmd.Parameters.AddWithValue("@categoriecode", categorie);
                sqlCmd.Parameters.AddWithValue("@catalogusprijs", catalogus);
                sqlCmd.Parameters.AddWithValue("@status", status);
                sqlCmd.Parameters.AddWithValue("@uitgevercode", uitgever);
                sqlCmd.ExecuteNonQuery();
            }
        }
    }

}
